﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Library;
using Lamex.Interface;
using Lamex.Base;
using DevExpress.XtraGrid.Views.Grid;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Lamex.SIESystemManager.Calculate;
using Lamex.Library.CommonClass;
using DevExpress.XtraGrid;

namespace Lamex.SIESystemManager.UI
{
    public partial class FrmBIEBOMWorkHoursBack : Lamex.Library.frmBaseChild
    {
        public static string Version { get; private set; }

        public FrmBIEBOMWorkHoursBack()
        {
            InitializeComponent();
            this.Load += new EventHandler(FrmBIEBOMWorkHoursBack_Load);

        }
        private void FrmBIEBOMWorkHoursBack_Load(object sender,EventArgs e)
        {
            try { base.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理
            radioGroup1.SelectedIndex = 1;
            DateEditFormat();
            dateStart.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            dateEnd.Text = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
        }
        public void DateEditFormat()
        {
            this.dateStart.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.dateStart.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dateStart.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.dateStart.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dateStart.Properties.Mask.EditMask = "yyyy-MM-dd";

            this.dateEnd.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.dateEnd.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dateEnd.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.dateEnd.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.dateEnd.Properties.Mask.EditMask = "yyyy-MM-dd";
        }
        public override void DoSJ(IButtonInfo sender)
        {
            #region 按钮权限
            if (!BuildSqlHelper.IsExistList("B_IE_WorkTimeHistory", "LamexMESManager")) return;
            #endregion
            SplashScreenTool.ShowSplashScreen(typeof(WaitForm1));
            //using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在计算工时......", new Size(400, 80)))
            //{
            string sql = @"select [PartNumber]
      ,[DrawNum]
      ,[Width_1]
      ,[Dedth_1]
      ,[Width_2]
      ,[Dedth_2]
      ,[Tinckness]
      ,[D_Cutting_Size]
      ,[WorkCenterNumber]
      ,[Decription]
      ,[GROUP_CODE]
      ,[CountDecription]
      ,[iCount]
      ,[Cycle]
      ,[D01]
      ,[D04]
      ,[D07]
      ,[D11]
      ,[D14]
      ,[D17]
      ,[D21]
      ,[D24]
      ,[D30]
      ,[D27]
      ,[D28]
      ,[D29]
      ,[D31]
      ,[D32]
      ,[D34]
      ,[D37]
      ,[D41]
      ,[D42]
      ,[D44]
      ,[D47]
      ,[D51]
      ,[D54] from [LamexMESManager].[dbo].[B_IE_WorkTimeHistory] where 
CreateDate>='" + dateStart.Text + "' and CreateDate<='" + dateEnd.Text + "' and Flag='4'";
            string sqlwhere = "";
            if (radioGroup1.SelectedIndex == 1)
                sqlwhere = "and ISNULL(Export_Flag,'')='' ";
            System.Data.DataTable dt = new System.Data.DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql + sqlwhere);
            gridView.DataSource = dt;
            gridView1.BestFitColumns();
            this.gridView1.OptionsView.ColumnAutoWidth = false;
            SplashScreenTool.CloseSplashScreen();
        }
        public override void DoSTD(IButtonInfo sender)
        {
            #region 按钮权限
            if (!BuildSqlHelper.IsExistList("B_IE_WorkTimeHistory", "LamexMESManager")) return;
            #endregion
            SplashScreenTool.ShowSplashScreen(typeof(WaitForm1));
            //using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在计算工时......", new Size(400, 80)))
            //{
            string sql = @"select [PartNumber]
      ,[DrawNum]
      ,[Width_1]
      ,[Dedth_1]
      ,[Width_2]
      ,[Dedth_2]
      ,[Tinckness]
      ,[D_Cutting_Size]
      ,[WorkCenterNumber]
      ,[Decription]
      ,[GROUP_CODE]
      ,[CountDecription]
      ,[iCount]
      ,[Cycle]
      ,[D01]
      ,[D04]
      ,[D07]
      ,[D11]
      ,[D14]
      ,[D17]
      ,[D21]
      ,[D24]
      ,[D30]
      ,[D27]
      ,[D28]
      ,[D29]
      ,[D31]
      ,[D32]
      ,[D34]
      ,[D37]
      ,[D41]
      ,[D42]
      ,[D44]
      ,[D47]
      ,[D51]
      ,[D54] from [LamexMESManager].[dbo].[B_IE_WorkTimeHistory] where 
CreateDate>='" + dateStart.Text + "' and CreateDate<='" + dateEnd.Text + "' and Flag='3'";
            string sqlwhere="";
            if (radioGroup1.SelectedIndex == 1)
                sqlwhere = "and ISNULL(Export_Flag,'')='' ";
            System.Data.DataTable dt = new System.Data.DataTable();
            dt = BaseBuildSql.GetDataTableBySql(sql + sqlwhere);
            gridView.DataSource = dt;
            gridView1.BestFitColumns();
            this.gridView1.OptionsView.ColumnAutoWidth = false;
            SplashScreenTool.CloseSplashScreen();
        }
        public override void DoExport(IButtonInfo sender)
        {
            //#region 导出方法1
            //try
            //{
            //    SaveFileDialog dlg = new SaveFileDialog();
            //    //dlg.Filter = "PDF file(*.pdf)|*.pdf";
            //    dlg.Filter = "Excel文件(*.xls)|*.xls";
            //    DialogResult dialogResult = dlg.ShowDialog(this);
            //    if (dialogResult == DialogResult.OK)
            //    {
            //        gridView1.ExportToXls(dlg.FileName);
            //        DevExpress.XtraEditors.XtraMessageBox.Show("导出成功", "提示", MessageBoxButtons.OK);
            //    }
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
            //#endregion

            //导出方法2
            //if (gridView1.RowCount < 0) return;
            //ExportToExcel(gridView1, "BOM工时计算");
            //base.DoExport(sender);

            //导出方法3
            if (!Directory.Exists(@"C:\SAPTEMP"))
                Directory.CreateDirectory(@"C:\SAPTEMP");
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出txt";
            fileDialog.InitialDirectory = @"C:\SAPTEMP";//导出默认路径
            fileDialog.Filter = "Txt文件(*.txt)|*.txt";//导出默认格式
            fileDialog.FileName = "ZPYX_UPLOAD";//导出默认名称
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                if (gridView1.DataRowCount <= 0) return;
                List<string> list1 = new List<string>();
                StringBuilder s1 = new StringBuilder();
                System.Data.DataTable dt = (gridView1.DataSource as System.Data.DataView).Table;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    list1.Add(dt.Rows[i]["PartNumber"].ToString());
                }
                s1.AppendFormat(@" AND PartNumber IN ('{0}')", string.Join("','", list1.ToArray()));
                string sql = @" SELECT 
       [PartNumber] as '部件/组件'
      ,[Decription] as '描述'
      ,[GROUP_CODE] as '组'
      ,[CountDecription] as '计数器描述'
      ,[iCount] as '计数器'
      ,[Cycle] as '周期(H)'
      ,[D01] as 'D01_主线-自动仓库'
      ,[D04] as 'D04_主线-开料LS1'
      ,[D07] as 'D07_主线-COMBIMA'
      ,[D11] as 'D11_主线-钻孔P&H'
      ,[D14] as 'D14_主线-开料FH4'
      ,[D17] as 'D17_主线-翻板机'
      ,[D21] as 'D21_主线-BIMA'
      ,[D24] as 'D24_小板线'
      ,[D30] as 'D30_斜边线-数控钻'
      ,[D27] as 'D27_主线装配区'
      ,[D28] as 'D28_斜边线-斜封边'
      ,[D29] as 'D29_斜边线-直封边'
      ,[D31] as 'D31_异型线-直封边'
      ,[D32] as 'D32_斜边线-CNC'
      ,[D34] as 'D34_异型线-手工封边'
      ,[D37] as 'D37_异型线-毛刷组装'
      ,[D41] as 'D41_木工加工区'
      ,[D42] as 'D42_配套区'
      ,[D44] as 'D44_柜桶组装'
      ,[D47] as 'D47_柜子组装'
      ,[D51] as 'D51_包装'
      ,[D54] as 'D54_配件包装'     
  FROM [LamexMESManager].[dbo].[B_IE_WorkTimeHistory]
  where 1=1 " + s1.ToString() + "";

                System.Data.DataTable dtList = BaseBuildSql.GetDataTableBySql(sql);

                GridControl grid = new GridControl();
                GridView view = new GridView();
                grid.ViewCollection.Add(view);
                grid.MainView = view;
                view.GridControl = grid;
                foreach (DataColumn dc in dtList.Columns)
                {
                    DevExpress.XtraGrid.Columns.GridColumn gridColumnNumber = view.Columns.AddVisible(dc.ColumnName);
                    gridColumnNumber.FieldName = dc.ColumnName;
                }
                grid.DataSource = dtList;
                this.Controls.Add(grid);
                grid.ForceInitialize();
                view.BestFitColumns();
                //view.OptionsPrint.PrintHorzLines = false;
                //view.OptionsPrint.PrintVertLines = false;
                view.ExportToText(fileDialog.FileName);
                //更新导出标识
                string Update_sql = @"update [LamexMESManager].[dbo].[B_IE_WorkTimeHistory] set Export_Flag='1' where 1=1 " + s1.ToString() + "";
                BaseBuildSql.ExecuteNonQuery(Update_sql);

                // DevExpress.XtraEditors.XtraMessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                    System.Diagnostics.Process.Start(fileDialog.FileName);//打开指定路径下的文件

            }
        }

        public  void ExportToExcel(GridView gridView, string 文件名)
        {
           
            System.Data.DataTable dt = (gridView.DataSource as System.Data.DataView).Table;
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Excel文件(*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;            //dlg.CreatePrompt = true;            
            dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            dlg.FileName = string.Format("{0}_{1}", DateTime.Now.ToString("yyyy-MM-dd HH时mm分"), 文件名);
            if (dlg.ShowDialog() != DialogResult.OK) return;
            string filePath = dlg.FileName;
            using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在导出数据...", new Size(400, 80)))
            {
                int OLDOFFICEVESION = -4143;
                int NEWOFFICEVESION = 56;                //保存excel文件的格式                
                int FormatNum;                //excel版本号                string Version;                //启动应用                
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;                //创建文件                
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);                //创建sheet                
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];                //获取你使用的excel 的版本号                
                Version = xlApp.Version;                //使用Excel 97-2003                
                if (Convert.ToDouble(value: Version) < 12)
                {
                    FormatNum = OLDOFFICEVESION;
                }                //使用 excel 2007或更新                
                else
                {
                    FormatNum = NEWOFFICEVESION;
                }
                //添加输出excel表格的表头信息信息                
                //注意这里的excel对应的单元格第一个位置为[1,1]，而不是我们平时定义[0,0]                
                for (int i = 0; i < gridView.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = gridView.Columns[i].FieldName;
                }
                //添加输出excel表格的内容信息                
                for (int rowIndex = 1; rowIndex < dt.Rows.Count + 1; rowIndex++)
                {
                    DataRow rowArr = dt.Rows[rowIndex - 1];
                    for (int cellIndex = 0; cellIndex < gridView.Columns.Count; cellIndex++)
                    {
                        string aa = rowArr[cellIndex].ToString();
                        worksheet.Cells[rowIndex + 1, cellIndex + 1] = rowArr[gridView.Columns[cellIndex].FieldName];
                    }
                }
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 2]).EntireColumn.Delete(0);
                //删除已存在的excel文件，否则会无法保存创建的excel文件                
                if (File.Exists(filePath))
                {
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (IOException e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                //保存，这里必须指定FormatNum文件的格式，否则无法打开创建的excel文件                
                workbook.SaveAs(filePath, FormatNum);
                //显示创建的excel文件，true打开Excel，false隐藏Excel                
                xlApp.Visible = false;                //关闭Excel进程                
                xlApp.Quit();
            }

        }
        //删除指定列
        private void DeleteColumn(Worksheet _worksheet, string columns, int startRow)
        {
            if (columns.Equals(""))
                return;
            string[] cns = columns.Split(',');
            string dropCn = "";
            foreach (string cn in cns)
            {
                if (dropCn == "")
                {
                    dropCn = cn + startRow + ":" + cn + (startRow + 2);
                }
                else
                {
                    dropCn = dropCn + "," + cn + startRow + ":" + cn + (startRow + 2);
                }
            }
            _worksheet.get_Range(dropCn, Type.Missing).EntireColumn.Delete(XlDeleteShiftDirection.xlShiftToLeft);  //删除整列           
        }
    }
    
}